﻿using BySoft.Server.Common;
using BySoft.Server.Common.Entity;
using OneSoft.DataAccess;
using OneSoft.EntityCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace BySoft.Server.DataAccess
{
    public partial class BsOutorderDA
    {
        public static DataTable SelectList(string strWorkOrderNo, string strBackMaterialID, string strFrom, string strEnd, int pageIndex, int PageSize, out int m_count)
        {
            try
            {
                string strcmd = @"SELECT a.*,
         m.MaterialTypeID, m.MaterialName, m.Specifications, m.IsDirect, m.Unit,
         mmt.MaterialTypeName
FROM BS_OutOrder AS a

  LEFT JOIN MD_Material AS m ON m.MaterialID = a.BackMaterialID
  LEFT JOIN MD_MaterialType AS mmt ON mmt.MaterialTypeID = m.MaterialTypeID
  WHERE a.[Status]='1' ";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                if (!string.IsNullOrEmpty(strWorkOrderNo))
                {
                    strcmd += " and a.WorkOrderNo like @strWorkOrderNo";
                    dpc.AddWithValue("@strWorkOrderNo", "%" + strWorkOrderNo + "%");
                }

                if (!string.IsNullOrEmpty(strWorkOrderNo))
                {
                    strcmd += " and a.BackMaterialID like @strBackMaterialID";
                    dpc.AddWithValue("@strBackMaterialID", "%" + strBackMaterialID + "%");
                }
                if (!string.IsNullOrEmpty(strFrom))
                {
                    strcmd += " and a.BillDate >= @strFrom";
                    dpc.AddWithValue("@strFrom", strFrom);
                }
                if (!string.IsNullOrEmpty(strEnd))
                {
                    strcmd += " and a.BillDate <= @strEnd";
                    dpc.AddWithValue("@strEnd", strEnd );
                }
                strcmd += " order by a.BillDate desc";



                DataTable  dt = DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);
         
               
               return FillBackProcessID(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 写入下一工序、工序号
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        private static DataTable FillBackProcessID(DataTable dt)
        {
            foreach (DataRow dr in dt.Rows)
            {
                string strScheduleDetailID = CommonMethod.GetString(dr["ScheduleDetailID"]);
                string strOutOrderNum = CommonMethod.GetString(dr["OutOrderNum"]);

                DataTable dtBack = SelectBackProcessID(strScheduleDetailID, strOutOrderNum);
                if (dtBack.Rows.Count > 0)
                {
                    dr["BackProcessID"] = dtBack.Rows[0]["BackProcessID"];
                    dr["BackOrderNum"] = dtBack.Rows[0]["BackOrderNum"];
                }
           
            }

            return dt;
        }

      
  
        /// <summary>
        /// 查询委外的工序ID
        /// </summary>
        /// <param name="strBillID">本单号码，新增时为空</param>
        /// <param name="strProcessID">工序</param>
        /// <returns></returns>
        public static DataTable SelectListProcessID( string strProcessID,string strWorkOrderNo, int pageIndex, int pageSize,out int  m_count)
        {
            try
            {
                
                //可委外的
                string strcmd = @"  SELECT bsd.ScheduleDetailID,bsd.WorkOrderNo,bsd.PlanID,bsd.ScheduleID,bsd.ScheduleNum,bsd.ExpectInStockDate, bsd.Remark,
         
         bsp.ProcessID,mp.ProcessName, bsp.IsOutSource, bsp.WarnNum
   FROM BS_Schedule AS bs 
   left join BS_ScheduleDetail AS bsd ON bsd.ScheduleID = bs.ScheduleID
   LEFT JOIN [BS_ScheduleProcess] AS bsp ON bsp.ScheduleDetailID = bsd.ScheduleDetailID 
  
  LEFT JOIN MD_Process AS mp ON mp.ProcessID = bsp.ProcessID
  WHERE bs.[Status]='1' AND bsp.IsOutSource='1'


";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();
             
                
              
                if (!string.IsNullOrEmpty(strProcessID))
                {
                    strcmd += " and ProcessID = @ProcessID";
                    dpc.AddWithValue("@ProcessID", strProcessID);
                }

                if (!string.IsNullOrEmpty(strWorkOrderNo))
                {
                    strcmd += " and WorkOrderNo like  @WorkOrderNo";
                    dpc.AddWithValue("@WorkOrderNo", "%"+strWorkOrderNo+"%");
                }
                strcmd += " order by ProcessID ";
                return DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, pageSize, dpc, out m_count);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

      


        /// <summary>
        /// 查询工序待委外的Material明细
        /// </summary>
        /// <param name="strBillID">本单号码，新增时为空</param>
        /// <param name="strProcessID">工序</param>
        /// <returns></returns>
        public static DataTable SelectOutProcessDetail(string strScheduleDetailID, string strProcessID)
        {
            try
            {

               
                string strcmd = @"SELECT  bsd.ScheduleDetailID , bsd.WorkOrderNo,bsd.ScheduleID,bsd.PlanID,bsd.ExpectInStockDate, bsd.Remark,
          bsp.ProcessID,bsp.ProcessID as OutPorcessID,bsp.OrderNum as OutOrderNum,
           bsp.ProcessID as BackProcessID,bsp.OrderNum as BackOrderNum, 
           bsd.ScheduleNum AS BackNums,
            bsm.MaterialID, bsm.TotalNum as [Nums],bsm.ActulSendDate,bsm.ActulSendNum,
         m.MaterialTypeID, m.MaterialName, m.Specifications, m.IsDirect, m.Unit,
         mmt.MaterialTypeName,mp.ProcessName
   FROM BS_Schedule AS bs 
   left join BS_ScheduleDetail AS bsd ON bsd.ScheduleID = bs.ScheduleID
    LEFT JOIN [BS_ScheduleProcess] AS bsp ON bsp.ScheduleDetailID = bsd.ScheduleDetailID 
    LEFT JOIN [BS_ScheduleMat] AS bsm ON bsm.ScheduleDetailID = bsd.ScheduleDetailID
    LEFT JOIN MD_Material AS m ON m.MaterialID = bsm.MaterialID
    LEFT JOIN MD_MaterialType AS mmt ON mmt.MaterialTypeID = m.MaterialTypeID
    LEFT JOIN MD_Process AS mp ON mp.ProcessID=bsp.ProcessID 
     WHERE bs.[Status]='1' AND bsp.IsOutSource='1'

";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                if (!string.IsNullOrEmpty(strScheduleDetailID))
                {
                    strcmd += " and bsd.ScheduleDetailID = @ScheduleDetailID";
                    dpc.AddWithValue("@ScheduleDetailID", strScheduleDetailID);
                }

               
                if (!string.IsNullOrEmpty(strProcessID))
                {
                    strcmd += " and bsp.ProcessID = @ProcessID";
                    dpc.AddWithValue("@ProcessID", strProcessID);
                }
                strcmd += " order by MaterialID ";
                DataTable dt = DataAccessCommon.Select(strcmd,dpc).Tables[0];
                return FillBackProcessID(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }



        //v0608 next processID

        private static DataTable SelectBackProcessID(string strScheduleDetailID, string strCurrentOrderNum)
        {
            try
            {
                if (string.IsNullOrEmpty(strScheduleDetailID))
                {
                    return new DataTable();
                }

                string strcmd = @" SELECT top 1 ProcessID as BackProcessID,isnull(OrderNum,0) as   BackOrderNum FROM BS_ScheduleProcess AS bsp
		  WHERE ScheduleDetailID='2'
              and  ScheduleDetailID=@ScheduleDetailID
    		  AND OrderNum >@OrderNum
          ORDER BY OrderNum 
";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                dpc.AddWithValue("@ScheduleDetailID", strScheduleDetailID);
                dpc.AddWithValue("@OrderNum", strCurrentOrderNum);
                return DataAccessCommon.Select(strcmd, dpc).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 还可回厂材料数量
        /// </summary>
        /// <param name="strCurrentReturnBillID"></param>
        /// <param name="sendID"></param>
        /// <param name="materialID"></param>
        /// <returns></returns>
        public static DataTable SelectMatCanBackNum(string strCurrentReturnBillID, string sendID, string materialID)
        {


            try
            {

                //未填过
                string strcmd = @"SELECT a.BackMaterialID,a.BillID,
       a.BackNums,
      
       a.BackNums -ISNULL(ret.returnNums, 0) AS CanBackNum --可退数量 
      
from Bs_OutOrder a

LEFT JOIN  (
        ---已退数量
          select OutOrderID,materialID,sum(Nums) as returnNums 
            from Bs_Outbackmat aR 
             where aR.BillID<>@CurrentBillID
             group by  OutOrderID,materialID
             )  ret on ret.OutOrderID=a.BillID and ret.materialID=a.BackMaterialID
WHERE  a.BackNums -ISNULL(ret.returnNums, 0)>0

    

";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                dpc.AddWithValue("@CurrentBillID", strCurrentReturnBillID);//排除本单以外的数量

                if (!string.IsNullOrEmpty(sendID))
                {
                    strcmd += " and a.BillID = @BillID";
                    dpc.AddWithValue("@BillID", sendID);
                }

                if (!string.IsNullOrEmpty(materialID))
                {
                    strcmd += " and a.BackMaterialID = @MaterialID";
                    dpc.AddWithValue("@MaterialID", materialID);
                }

                strcmd += " order by BillID ";

                DataTable dt = DataAccessCommon.Select(strcmd, dpc).Tables[0];


                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        /// <summary>
        ///  还能退回多少原料
        /// </summary>
        /// <param name="strCurrentReturnBillID"></param>
        /// <param name="sendID"></param>
        /// <param name="materialID"></param>
        /// <returns></returns>
        public static DataTable SelectSendDetailCanReturnNum(string strCurrentReturnBillID, string sendID, string materialID)
        {


            try
            {

                
                string strcmd = @"SELECT d.MaterialID,d.BillID,
       d.Nums                           as SendNum,
      
       d.nums -ISNULL(ret.returnNums, 0) AS CanBackNum --可退数量 
      
from Bs_OutOrder a
left join Bs_OutOrderDetail d on a.BillID=d.BillID
LEFT JOIN  (
        ---已退数量
          select OutOrderID,dr.materialID,sum(dr.Nums) as returnNums 
            from Bs_Outbackmat aR 
             LEFT JOIN Bs_OutmatReturnDetail dR on aR.billId=dR.BillID 
              where aR.BillID<>@CurrentBillID
             group by  OutOrderID,dr.materialID
             )  ret on ret.OutOrderID=a.BillID and ret.materialID=d.materialID
WHERE  d.nums -ISNULL(ret.returnNums, 0)>0
    

";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                dpc.AddWithValue("@CurrentBillID", strCurrentReturnBillID);//排除本单以外的数量

                if (!string.IsNullOrEmpty(sendID))
                {
                    strcmd += " and d.BillID = @BillID";
                    dpc.AddWithValue("@BillID", sendID);
                }

                if (!string.IsNullOrEmpty(materialID))
                {
                    strcmd += " and d.MaterialID = @MaterialID";
                    dpc.AddWithValue("@MaterialID", materialID);
                }

                strcmd += " order by BillID ";

                DataTable dt = DataAccessCommon.Select(strcmd, dpc).Tables[0];


                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        #region View Data
        public static DataTable SelectHeadData(string strBillID)
        {
            try
            {
                string strcmd = @"select  a.*,m.MaterialName,s.SupplierName,mp.ProcessName as OutProcessName,
                                  -1 as IsNew,-1 as IsDel
from Bs_OutOrder a
LEFT JOIN md_material m ON a.BackMaterialID=m.MaterialID
LEFT JOIN md_supplier s  on a.SupplierID =s.SupplierID
LEFT JOIN MD_Process AS mp ON mp.ProcessID=a.OutProcessID  
 
where a.BillID=@BillID";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                dpc.AddWithValue("@BillID", strBillID);
                DataTable dt= DataAccessCommon.Select(strcmd, dpc).Tables[0];
                return FillBackProcessID(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public static DataTable SelectDetailData(string strBillID)
        {
            try
            {
                string strcmd = @"select a.*,m.MaterialName,m.Specifications,m.Unit,
                 mt.MaterialTypeName,
                 mmst.MaterialSmallTypeName,
                -1 as IsNew,-1 as IsDel
 from Bs_OutOrderDetail a
LEFT JOIN md_material m ON a.MaterialID=m.MaterialID
LEFT JOIN md_materialType mt ON m.MaterialTypeID=mt.MaterialTypeID
LEFT  JOIN MD_MaterialSmallType AS mmst ON mmst.MaterialSmallTypeID = m.MaterialSmallTypeID
where a.BillID=@BillID";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                dpc.AddWithValue("@BillID", strBillID);
                return DataAccessCommon.Select(strcmd, dpc).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }

        } 
        #endregion



        #region AUD
        public static bool SaveOrUpdate(BsOutorderEntity entity, DataTable OutorderDetailDT, bool isNew)
        {
            bool istag = true;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                broker.BeginTransaction();
                try
                {
                    if (isNew)
                    {
                        DataAccessCommon.Insert(entity, broker);
                    }
                    else
                    {
                        DataAccessCommon.UpdateOnly(entity, broker);
                        ClsCommDA.Del("BS_OutOrderDetail", "BillID",entity.BillID, broker);//delete  first  & then insert new 
                     

                    }
                    if (OutorderDetailDT != null && OutorderDetailDT.Rows.Count > 0)
                    {
                        foreach (DataRow drItem in OutorderDetailDT.Rows)
                        {
                            BsOutorderdetailEntity DetailEntity = new BsOutorderdetailEntity();
                            DetailEntity.BillID = entity.BillID;
                            DetailEntity.Nums = CommonMethod.ConvertToDecimal(drItem["Nums"]);
                            DetailEntity.MaterialID = CommonMethod.GetString(drItem["MaterialID"]);

                            DetailEntity.Remark = CommonMethod.GetString(drItem["Remark"]);
                     
                            string strIsNew = CommonMethod.GetString(drItem["IsNew"]);
                            string strIsDel = CommonMethod.GetString(drItem["IsDel"]);
                            if (strIsNew == "1")//insert
                            {
                                DataAccessCommon.Insert(DetailEntity, true, broker);
                            }
                            else
                            {
                                if ( strIsDel != "1")//update
                                {
                                    //DetailEntity.DetailID = CommonMethod.ConvertToDecimal(drItem["DetailID"]);
                                    //DataAccessCommon.UpdateOnly(DetailEntity, broker);
                                    DataAccessCommon.Insert(DetailEntity, true, broker);
                                
                                }
                                else if ( strIsDel == "1")
                                {
                                    //删除明细  不会用到这里，@See Del()
                                    ClsCommDA.Del("BS_OutOrderDetail", "DetailID", CommonMethod.GetString(drItem["DetailID"]), broker);
                                }
                               

                            }
                           
                        }
                    }
                    broker.Commit();
                }
                catch
                {
                    istag = false;
                    broker.Rollback();
                    throw;
                }
                return istag;
            }
        }

        public static bool Del(string strCode)
        {
            bool isResult = true;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    DataAccessCommon.Delete(strCode, "BillID", "BS_OutorderDetail");
                    DataAccessCommon.Delete(strCode, "BillID", "BS_Outorder");
                    broker.Commit();
                }
                catch (Exception ex)
                {
                    isResult = false;
                    broker.Rollback();
                    throw ex;
                }
            }
            return isResult;
        } 
        #endregion
    }
}
